package mya_dc.compilation_server.database;

import java.io.File;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import org.h2.tools.Server;
import mya_dc.shared_classes.*;

/**
 * Static class that handles the Compilation Server database.
 * The database is implemented with JDBC technology, using the h2 driver.
 * The database contains two tables: 
 * <li>Projects Table: contains the fields: 
 * <dd>Project Name 
 * <dd>User Name
 * <dd>Project Size
 * <dd>Last Access Timestamp.
 * <li>Compilations Table: 
 * <dd>Project Name
 * <dd>User Name
 * <dd>Compilation Timestamp.
 * @author      Marina Skarbovsky
 * <dd>			MYA
 */
public class DatabaseManager {

	private DatabaseManager() {}
	
	/**
	 * Initializes the database in the server's root working directory under the folder "database".
	 * The name of the database is MYADatabase, and the administrator is registered under the user name "sa".
	 * 
	 * @param sWorkingDirectory - The directory where the database is created.
	 * @throws ClassNotFoundException - The driver's class cannot be loaded.
	 * @throws SQLException - The database cannot be loaded.
	 */
    public static void initializeDatabase(String sWorkingDirectory) throws ClassNotFoundException, SQLException
    {
    	m_sWorkingDirectory = sWorkingDirectory;
    	Class.forName(m_sDriverClassName);
    	String[] args = {"-tcp", "-tcpPort", "1024"};  
    	m_TCPServer = Server.createTcpServer(args).start();
    	m_DBConnection = DriverManager.getConnection("jdbc:h2:" + m_TCPServer.getURL() + "/" +  m_sWorkingDirectory + m_sDatabaseName + ";DB_CLOSE_ON_EXIT=FALSE" ,m_sUserName,"");
    	try
    	{
    		String sProjectTableCreation = "create table PROJECTS" + "(ProjectName varchar(256),UserName varchar(256),ProjectSize bigint,LastAccess timestamp)";
    		String sCompilationTableCreation = "create table COMPILATION" +  "(UserName varchar(256),ProjectName varchar(256), CompilationTime timestamp)";
    		m_DBConnection.createStatement().executeUpdate(sProjectTableCreation);
    		m_DBConnection.createStatement().executeUpdate(sCompilationTableCreation);
    		m_DBConnection.commit();
    	}
    	catch(Exception e) {}
    }
    
    
	/**
	 * Adds a new compilation entry to the compilation table.
	 * 
	 * @param sUserName - Name of the user performing the compilation.
	 * @param sProjectName - Name of the project being compiled.
	 * @param CurrTime - Timestamp containing compilation time.
	 */
    public static synchronized void addCompilationEntry(String sUserName, String sProjectName, Timestamp CurrTime) 
    {
    	try
    	{
        	PreparedStatement AddCompilationEntry = m_DBConnection.prepareStatement(m_sAddCompilation);
        	AddCompilationEntry.setString(1,sUserName);
        	AddCompilationEntry.setString(2,sProjectName);
        	AddCompilationEntry.setTimestamp(3,CurrTime);
    		AddCompilationEntry.executeUpdate();
    		m_DBConnection.commit();
    	}
    	catch (SQLException e){}
    }
    
	/**
	 * Builds a UserProject array out of the projects in the database
	 * 
	 * @return ArrayList of UserProject objects.
	 */
    public static ArrayList<UserProject> getUserProjectArray()
    {
    	try
    	{
    		 ArrayList<UserProject> Projects = new ArrayList<UserProject>();
    		 ResultSet ProjectsList = m_DBConnection.createStatement().executeQuery("select * from PROJECTS");
    		 while (ProjectsList.next())
    		 {
    			 Projects.add(new UserProject(ProjectsList.getString(2),ProjectsList.getString(1)));
    		 }
    		 if (Projects.isEmpty())
    		 {
    			 return null;
    		 }
    		 else
    		 {
    			 return Projects;
    		 }
    	}
    	catch(SQLException e)
    	{
    		return null;
    	}		
    }
    
    
	/**
	 * Removes all of the information in the database connected to the projects in the supplied list.
	 * 
	 * @param UserProjectList - ArrayList of UserProject objects, containing the projects to be removed.
	 */
    public static void removeUserProjectsInList(ArrayList<UserProject> UserProjectList)
    {
    	for (Iterator<UserProject> ListIterator = UserProjectList.iterator(); ListIterator.hasNext();)
    	{
    		UserProject CurrProject = ListIterator.next();
    		removeProjectEntry(CurrProject.m_sUserName, CurrProject.m_sProjectName);
    	}
    }
    
    
	/**
	 * Adds a new project entry to the projects table.
	 * 
	 * @param sProjectName - New project name.
	 * @param sUserName - User name.
	 * @param Size - Timestamp containing compilation time.
	 * @param LastAccess -  Timestamp containing last access time to the project.
	 */
    public static synchronized void addProjectEntry(String sProjectName, String sUserName , Long Size , Timestamp LastAccess) 
    {
    	try
    	{
        	PreparedStatement AddProject = m_DBConnection.prepareStatement(m_sAddProject);
        	AddProject.setString(1, sProjectName);
        	AddProject.setString(2,sUserName);
        	AddProject.setLong(3,Size);
        	AddProject.setTimestamp(4, LastAccess);
    		AddProject.executeUpdate();
    		m_DBConnection.commit();
    	}
    	catch (SQLException e)
    	{}
	}
    
    
	/**
	 * Returns the value in the cell pointed by the row and column indexes, in the ResultSet received
	 * after executing the supplied query.
	 * 
	 * @param sQuery - Query to be executed.
	 * @param nRowIndex - Row index.
	 * @param nColumnIndex - Column index.
	 * 
	 * @return Cell contents.
	 */
    public static Object getCellContents(String sQuery ,int nRowIndex, int nColumnIndex)
    {
    	try
    	{
    		ResultSet QueryResults = m_DBConnection.createStatement().executeQuery(sQuery);
    		if (QueryResults == null)
    		{
    			return null;
    		}
    		for(int i=0; i < nRowIndex; i++)
    		{
    			if (!QueryResults.next())
    			{
    				return null;
    			}
    		}	
    		return QueryResults.getObject(nColumnIndex);
    	}
    	catch (SQLException e)
    	{
    		return null;
    	}
    }
    
    
	/**
	 * Returns the row pointed by the row index in the projects table, as an object array.
	 * 
	 * @param nRowIndex - Row index.
	 * 
	 * @return The row.
	 */
    public static Object[] getProjectTableRow(int nRowIndex)
    {
    	try
    	{
    		ResultSet QueryResults = m_DBConnection.createStatement().executeQuery("select * from PROJECTS");
    		if (QueryResults == null)
	    	{
	    		return null;
	    	}
	    	for(int i=0; i < nRowIndex; i++)
	    	{
	    		if (!QueryResults.next())
	    		{
	    			return null;
	    		}
	    	}
	    	Object[] Row = new Object[4];
	    	Row[0] = QueryResults.getString(1);
	    	Row[1] = QueryResults.getString(2);
	    	Row[2] = QueryResults.getLong(3);
	    	Row[3] = QueryResults.getTimestamp(4);
	    	return Row;
    	}
    	catch(SQLException e)
    	{
    		return null;
    	}
    }
    
    
	/**
	 * Returns the number of registered projects.
	 * 
	 * @return Project count.
	 */
    public static int countProjects() 
    {
    	try
    	{
        	PreparedStatement ProjectNumberQuery = m_DBConnection.prepareStatement(m_sProjectNumQuery);
    		ResultSet ProjectNumber = ProjectNumberQuery.executeQuery();
    		ProjectNumber.next();
    		return ProjectNumber.getInt("rowCount");
    	}
    	catch (SQLException e)
    	{
    		return 0;
    	}
    }
    
    
	/**
	 * Returns the number of registered users.
	 * 
	 * @return Users count.
	 */
    public static int countUsers() 
    {
    	try
    	{
        	PreparedStatement UserNumberQuery = m_DBConnection.prepareStatement(m_sUserNumQery);
    		ResultSet UserNumber = UserNumberQuery.executeQuery();
    		UserNumber.next();
    		return UserNumber.getInt("rowCount");
    	}
    	catch (SQLException e)
    	{
    		return 0;
    	}
    }
    
	/**
	 * Returns the number of compilation entries of a specific project.
	 * 
	 * @param sUserName - User name.
	 * @param sProjectName - Project name.
	 * 
	 * @return Compilation entry count.
	 */
    public static int countCompilations(String sUserName, String sProjectName)
    {
    	try
    	{
        	PreparedStatement CompilationsNumberQuery = m_DBConnection.prepareStatement(m_sProjectCompilationNumQuery);
        	CompilationsNumberQuery.setString(1,sUserName);
        	CompilationsNumberQuery.setString(2, sProjectName);
    		ResultSet CompilationsNumber = CompilationsNumberQuery.executeQuery();
    		CompilationsNumber.next();
    		return CompilationsNumber.getInt("rowCount");
    	}
    	catch (SQLException e)
    	{
    		return 0;
    	}
    }
    
    
	/**
	 * Removes all the compilation entries of a given project.
	 * 
	 * @param sUserName - User name.
	 * @param sProjectName - Project name.
	 * 
	 */
    private static synchronized void removeProjectCompilationEntries(String sProjectName, String sUserName) 
    {
    	try
    	{
        	PreparedStatement RemoveCompilationEntries = m_DBConnection.prepareStatement(m_sRemoveProjectCompilationEntries);
        	RemoveCompilationEntries.setString(1,sProjectName);
        	RemoveCompilationEntries.setString(2,sUserName);
    		RemoveCompilationEntries.executeUpdate();
    		m_DBConnection.commit();
    	}
    	catch (SQLException e){}
    }
    
    
	/**
	 * Removes a project entry from the database.
	 * Project's compilation entries will be removed as well.
	 * 
	 * @param sUserName - User name.
	 * @param sProjectName - Project name.
	 * 
	 * @return number of remaining registered projects of the user.
	 */
    public static synchronized int removeProjectEntry(String sUserName,String sProjectName)
    {
    	try
    	{
        	removeProjectCompilationEntries(sProjectName,sUserName);
        	PreparedStatement RemoveProject = m_DBConnection.prepareStatement(m_sRemoveProjectEntry);
        	RemoveProject.setString(1,sProjectName);
        	RemoveProject.setString(2,sUserName);
    		RemoveProject.executeUpdate();
    		m_DBConnection.commit();
    		return countUserProjectNumber(sUserName);
    	}
    	catch (SQLException e)
    	{
    		return 0;
    	}
    }
    
	/**
	 * Removes all of the projects associated with the user.
	 * Projects' compilation entries will be removed as well.
	 * 
	 * @param sUserName - User name.
	 * 
	 */
    public static synchronized void removeAllUserProjects(String sUserName)
    {
    	try
    	{
        	DatabaseManager.removeAllUserCompilationEntries(sUserName);
        	PreparedStatement RemoveAllUserProjects = m_DBConnection.prepareStatement(m_sRemoveAllUserProjects);
        	RemoveAllUserProjects.setString(1,sUserName);
    		RemoveAllUserProjects.executeUpdate();
			m_DBConnection.commit();
    	}
		catch (SQLException e){}
    }
    
	/**
	 * Removes all of the compilation entries associated with the user.
	 * 
	 * @param sUserName - User name.
	 * 
	 */
    private static synchronized void removeAllUserCompilationEntries(String sUserName)
    {
		try
		{
	    	PreparedStatement RemoveUserCompilationEntries = m_DBConnection.prepareStatement(m_sRemoveUserCompilationEntries);
	    	RemoveUserCompilationEntries.setString(1, sUserName);
			RemoveUserCompilationEntries.executeUpdate();
			m_DBConnection.commit();
		}
		catch (SQLException e) {}
    }
    
    
	/**
	 * Updates project's size.
	 * 
	 * @param NewSize - New size in bytes (long).
	 * @param sProjectName - Updated project name.
	 * @param sUserName - User name.
	 * 
	 */
    public static synchronized void updateProjectSize(Long NewSize ,String sProjectName,String sUserName)
    {	
    	try
    	{
        	PreparedStatement ProjectSizeUpdate = m_DBConnection.prepareStatement(m_sProjectSizeUpdate);
        	ProjectSizeUpdate.setLong(1,NewSize);
        	ProjectSizeUpdate.setString(2,sProjectName);
        	ProjectSizeUpdate.setString(3,sUserName);
    		ProjectSizeUpdate.executeUpdate();
    		m_DBConnection.commit();
    	}
    	catch (SQLException e){}
    }
    
	/**
	 * Updates project's last access time.
	 * 
	 * @param NewTimestamp - New timestamp.
	 * @param sProjectName - Updated project name.
	 * @param sUserName - User name.
	 * 
	 */
    public static synchronized void updateProjectTimestamp(Timestamp NewTimestamp ,String sProjectName,String sUserName)
    {	
    	try
    	{
        	PreparedStatement ProjectTimestampUpdate = m_DBConnection.prepareStatement(m_sProjectTimestampUpdate);
        	ProjectTimestampUpdate.setTimestamp(1,NewTimestamp);
        	ProjectTimestampUpdate.setString(2,sProjectName);
        	ProjectTimestampUpdate.setString(3,sUserName);
    		ProjectTimestampUpdate.executeUpdate();
    		m_DBConnection.commit();
    	}
    	catch (SQLException e){}
    }

    
	/**
	 * Count the number of registered projects of the given user.
	 * 
	 * @param sUserName - User name.
	 * 
	 * @return User's project count
	 * 
	 */
    public static synchronized int countUserProjectNumber(String sUserName)
    {
    	try
    	{
        	PreparedStatement ProjectExistsQuery = m_DBConnection.prepareStatement( m_sUsersProjectNumber);
        	ProjectExistsQuery.setString(1,sUserName);
    		ResultSet UserProjectCount = ProjectExistsQuery.executeQuery();
    		UserProjectCount.first();
    		return UserProjectCount.getInt("rowCount");
    	}
    	catch (SQLException e)
    	{
    		return 0;
    	}
    }
    
   
	/**
	 * Checks whether a project is registered in the database.
	 * 
	 * @param sUserName - User name.
	 * @param sProjectName - Project name.
	 * 
	 * @return true if the project exists in the database, and false otherwise.
	 * 
	 */
    public static synchronized boolean checkProjectRegistrated(String sUserName, String sProjectName)
    {
    	try
    	{
        	PreparedStatement ProjectExistsQuery = m_DBConnection.prepareStatement(m_sProjectExistsQuery);
        	ProjectExistsQuery.setString(1,sUserName);
        	ProjectExistsQuery.setString(2,sProjectName);
    		ResultSet UserNames = ProjectExistsQuery.executeQuery();
    		UserNames.first();
    		if (UserNames.getInt("rowCount") > 0)
    			return true;
    		else
    			return false;
    	}
    	catch (SQLException e)
    	{
    		return false;
    	}
    }
    
    
	/**
	 * Opens the h2 console.
	 * 
	 * @throws IOException - The console cannot be opened.
	 */
    public static void displayConsole() throws IOException
    {
    	Runtime.getRuntime().exec(m_sConsoloeCommand, null, new File(m_sWorkingDirectory + "H2\\bin"));
    }
    
	/**
	 *Shuts down the database
	 */
    public static void shutDownDatabase()
    {
    	m_TCPServer.shutdown();
    }
    
	/**
	 *Returns database URL and working directory
	 */
    public static String[] getDatabaseSettings()
    {
    	String[] DatabaseInfo = new String[4];
    	DatabaseInfo[0] = m_sWorkingDirectory;
    	DatabaseInfo[1] = "jdbc:h2:" + m_TCPServer.getURL();
    	return DatabaseInfo;
    }
    	 
    	 
    //Member variables
    static Server m_TCPServer = null;
	static String m_sWorkingDirectory = null;	
	static Connection m_DBConnection = null;
	final static String m_sDatabaseName = "MYADatabase";
	final static String m_sDriverClassName = "org.h2.Driver";
	final static String m_sConsoloeCommand = "java -cp \"h2.jar;%H2DRIVERS%;%CLASSPATH%\" org.h2.tools.Console %*";
	final static String m_sUserName = "sa";
	final static String m_sAddProject = "insert into PROJECTS values(?,?,?,?)";
	final static String m_sAddCompilation = "insert into COMPILATION values(?,?,?)";
	final static String m_sRemoveProjectEntry = "delete from PROJECTS where ProjectName like ? and UserName like ?";
	final static String m_sRemoveAllUserProjects = "delete from PROJECTS where UserName like ?";
	final static String m_sRemoveProjectCompilationEntries = "delete from COMPILATION where ProjectName like ? and UserName like ?";
	final static String m_sRemoveUserCompilationEntries = "delete from COMPILATION where UserName like ?";
	final static String m_sProjectNumQuery = "select count(ProjectName) as rowCount from PROJECTS";
	final static String m_sUserNumQery = "select count(distinct UserName) as rowCount from PROJECTS";
	final static String m_sProjectCompilationNumQuery = "select count(UserName) as rowCount from COMPILATION where UserName like ? and ProjectName like ?";
	final static String m_sProjectSizeUpdate = "update PROJECTS set ProjectSize = ? where ProjectName like ? and UserName like ?";
	final static String m_sProjectTimestampUpdate = "update PROJECTS set LastAccess = ? where ProjectName like ? and UserName like ?";
	final static String m_sUsersProjectNumber = "select count(UserName) as rowCount from PROJECTS where UserName like ? ";
	final static String m_sProjectExistsQuery = "select count(UserName) as rowCount from PROJECTS where UserName like ? and ProjectName like ?";
}
